from flask import Flask, request, send_file, make_response
from flask_cors import CORS  # 导入CORS
import pandas as pd
from openpyxl import Workbook, load_workbook
import uuid
import logging
import traceback  # 导入traceback模块用于获取详细的异常信息
import os
from datetime import datetime

logging.basicConfig(level=logging.DEBUG)  # 设置日志级别为DEBUG，记录所有级别的日志
app = Flask(__name__)
CORS(app)  # 为Flask应用启用CORS


def get_tables(ws):
    s_e_index = {'start': [], 'end': []}
    for index, row in enumerate(ws.iter_rows()):
        cell_value = row[1].value
        if cell_value == '产品系列':
            s_e_index['start'].append(index)
        if len(s_e_index['end']) == len(s_e_index['start']) - 1 and index > s_e_index['start'][-1] and cell_value in [
            '订货明细', '订单政策', '合计',
            '政策比例：', '产品系列',
            None]:
            s_e_index['end'].append(index - 1)
    print(s_e_index)
    # ======================================================================================================================
    xlsx = pd.ExcelFile(r'F:\pythonProject2\pythonProject\Automatic_conversion\销售订单模板表0531.xlsx')
    # xlsx = '销售订单模板表0531.xlsx'
    # 获取工作表的总行数
    sheet_info = pd.read_excel(xlsx, sheet_name=0, nrows=0)
    total_rows = sheet_info.shape[0]
    # 根据找到的起始位置确定每张表的边界
    table_boundaries = {
        '订货明细': (s_e_index['start'][0], s_e_index['end'][0]),
        '订单政策': (s_e_index['start'][0], s_e_index['end'][0]),
        '宣赠品': (s_e_index['start'][0], s_e_index['end'][0])
    }
    # 读取每张表的数据
    tables = {}
    for table_name, (start, end) in table_boundaries.items():
        # 读取从start行到end行之间的数据
        tables[table_name] = pd.read_excel(xlsx, sheet_name=0, skiprows=start, nrows=end - start, usecols='B:I',
                                           dtype=str)
    # 打印每张表的数据

    # 关闭Excel文件
    xlsx.close()
    return tables


# 找到三张表的起始
# start_end_index = {'订货明细': [], '订单政策': [], '宣赠品': []}


# 找到客户名称
def find_customer(ws):
    result = []
    found = False
    # 遍历工作表中的单元格
    for row in ws.iter_rows():
        for cell in row:
            # 打印单元格的坐标和内容
            if cell.value is not None:
                if cell.value == '业务员：':
                    found = True
                    break
                result.append(cell.value)
        if found == True:
            return result[-1]


def automatic_conversion(df=None):
    logging.debug('成功接收到了文件,开始执行自动转换函数')
    result_list = []

    # 遍历DataFrame的每一行
    for index, row in df.iterrows():
        result_dict = {'commodity_code': row['货品编码'], 'offer_price': row['供货价'],
                       'quantity_purchased': row['采购数量'], 'number_of_bundles': row['搭赠数量']}

        # 将处理后的行添加到结果DataFrame中
        result_list.append(result_dict)

    # 返回处理后的DataFrame
    return result_list


def get_data(ws):
    result_list = []
    tables = get_tables(ws)
    for table_name, table in tables.items():
        if table_name == '订货明细':
            for index, row in table.iterrows():
                result_dict = {}
                result_dict['产品编码'] = row['产品编码']
                result_dict['销售单价'] = row['供货价']
                result_dict['数量'] = row['数量']
                result_dict['是否赠品'] = '否'
                result_list.append(result_dict)
        if table_name == '订单政策':
            for index, row in table.iterrows():
                result_dict = {}
                result_dict['产品编码'] = row['产品编码']
                result_dict['销售单价'] = row['单价']
                result_dict['数量'] = row['数量']
                result_dict['是否赠品'] = '是'
                result_list.append(result_dict)
        if table_name == '宣赠品':
            for index, row in table.iterrows():
                result_dict = {}
                result_dict['产品编码'] = row['产品编码']
                result_dict['销售单价'] = row['单价']
                result_dict['数量'] = row['数量']
                result_dict['是否赠品'] = '是'
                result_list.append(result_dict)
    return result_list


def create_workbook(customer_name: str, result_list: list):
    # 获取当前时间
    current_time = datetime.now()

    # 格式化为年/月/日的字符串
    formatted_time = current_time.strftime("%Y/%m/%d")
    # 创建工作簿
    wb = Workbook()
    # 删除默认的空sheet
    default_sheet = wb.active
    wb.remove(default_sheet)
    # 创建第一个sheet
    sheet1 = wb.create_sheet("订单")
    # 设置表头
    sheet1.append(["VirtualID-id", "制单员", "订单类型", "客户名称", "价格表名称", "所属部门", "下单时间", "业务类型",
                   "参加活动类型", "销售类型", "款项类型", "订单日期", "发货说明", "客户收货地址", "同步账套",
                   "仓库档案", "备注"])
    # 填写第二行的内容
    sheet1["A2"] = "VirtualID-1"
    sheet1["B2"] = "高子业"
    sheet1["C2"] = "大众健康事业部"
    sheet1["D2"] = customer_name
    sheet1["E2"] = "标准价格表"
    sheet1["G2"] = formatted_time
    sheet1["H2"] = "普通销售"
    sheet1["I2"] = "特殊客户"
    sheet1["J2"] = "正常销售"
    sheet1["K2"] = "应收款"
    sheet1["L2"] = formatted_time
    # 创建第二个sheet
    sheet2 = wb.create_sheet("订单明细")
    # 设置表头
    sheet2.append(
        ["订单编号", "业务类型", "产品编码", "销售单价", "数量-系统", "商品渠道", "是否赠品", "赠品类型", "赠品名称",
         "费用承担部门", "收入类型", "是否参加返利", "备注"])
    # 填写第二行的内容（这部分代码需要你自己添加）
    for index, result_dict in enumerate(result_list):
        sheet2.append(
            ['VirtualID-1', '业务申请及特殊客户', str(result_dict['commodity_code']), result_dict['offer_price'],
             result_dict['quantity_purchased'], '',
             '否', '', '', '', '营养品收入',
             '是', ''])
        sheet2.append(
            ['VirtualID-1', '业务申请及特殊客户', result_dict['commodity_code'], 0, result_dict['number_of_bundles'],
             '', '是', '', '', '漫步-天猫',
             '营养品收入', '是', ''])

    # 保存Excel文件
    excel_filename = "订单数据.xlsx"
    current_dir = os.path.dirname(__file__)  # 获取当前脚本所在的目录
    relative_path = os.path.join(current_dir, excel_filename)
    wb.save(relative_path)
    logging.debug(f'文件保存路径-->{relative_path}')
    return relative_path


@app.route('/convert', methods=['POST'])
def convert_excel():
    # 确保收到了文件
    if 'file' not in request.files:
        return 'No file part', 400

    file = request.files['file']

    # 如果用户没有选择文件，浏览器也会
    # 提交一个没有文件名的空部分
    if file.filename == '':
        return 'No selected file', 400

    if file:
        # 使用pandas读取上传的Excel文件
        wb = load_workbook(file, data_only=True)
        ws = wb.worksheets[0]
        wb.close()
        # 调用自动转换函数
        try:
            customer_name = find_customer(ws)
            result_list = get_data(ws)
        except Exception as e:
            error_message = f"Error occurred: {str(e)}"  # 构造错误信息字符串
            traceback_str = traceback.format_exc()  # 获取详细的异常堆栈信息
            # 记录错误信息到日志文件
            with open('error.log', 'a') as f:
                f.write(f"{error_message}\n{traceback_str}\n")
            # 向前端返回错误信息
            return "传入文件不合法，请检查文件格式或内容。"
        logging.debug(f'开始进行文档生成.....客户名称-->{customer_name}')
        excel_path = create_workbook(customer_name=customer_name, result_list=result_list)
        file_name = uuid.uuid4()
        # 发送文件给客户端
        result = send_file(excel_path, as_attachment=True, attachment_filename=f'{file_name}.xlsx')

        # 添加Access-Control-Expose-Headers
        response = make_response(result)
        response.headers['Access-Control-Expose-Headers'] = 'Content-Disposition'

        return response


if __name__ == '__main__':
    app.run(debug=True)
